using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//using System.Data.SqlClient;  // for MS SQL
//using System.Data.OleDb;      // for general connector
using MySql.Data.MySqlClient;  

namespace Project
{

    /** To Do List
     * 
     * 
     * data controls
     * Present data table
     * Figure out mass input into DB
     * Choose Hosting Provider
     * Look Feel Design
     * Payment Option
     * 
     */

    public partial class TestMySql : System.Web.UI.Page
    {
        String myConnectionString;
        MySqlDataReader dataReader;
        MySqlConnection myConn;
        
        protected void Page_Load(object sender, EventArgs e)
        {
            myConnectionString = "Database=test;Data Source=localhost;User Id=root;Password=mysql";
            myConn = new MySqlConnection(myConnectionString);
            myConn.Open();

            this.openMySQL();
            this.fillDropList();

            // Close the connection when done with it.
            myConn.Close();

        }

        protected void fillDropList() {
            string myInsertQuery = "SELECT name, category FROM FOOD"; // WHERE CATEGORY = 'CHICKEN'";
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConn);

            MySqlDataAdapter myAdapt = new MySqlDataAdapter(myCommand);
            DataSet ds = new DataSet();
            myAdapt.Fill(ds, "Food");
            DropList01.DataSource = ds.Tables["Food"];
            DropList01.DataTextField = "Category";
            DropList01.DataValueField = "Category";
            DropList01.DataBind();
        
        
        }

        protected void openMySQL() {
            string myInsertQuery = "SELECT * FROM FOOD"; // WHERE CATEGORY = 'CHICKEN'";
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
            myCommand.Connection = myConn;

            try
            {
                dataReader = myCommand.ExecuteReader();
            // Always call Read before accessing data.
            lblTest.Text = "";
            while (dataReader.Read())
            {
                //Console.WriteLine(dataReader.GetInt32(0) + ", " + 
                //    dataReader.GetString(1) + ", " + dataReader.GetString(2) + ", " +
                //    dataReader.GetFloat(3) + ", " + dataReader.GetString(4));
                lblTest.Text += dataReader.GetInt32(0) + ", " + 
                    dataReader.GetString(1) + ", " + dataReader.GetString(2) + ", " +
                    dataReader.GetString(3) + "<br>";
            }
            // always call Close when done reading.
            dataReader.Close();
            }
            catch (Exception e)
            {
                lblTest.Text = e.StackTrace;
            }

            
            
//            // Define the connection to Database
//            //<x>={Sql, OleDb, ...}
//            SqlConnection myConn = new SqlConnection();
//            //myConn.ConnectionString = "Provider=SQLOLEDB.1; Data Source=localhost;" + " Initial Catalog=Pubs; User ID=root";
//            // the provider is need when <x>=OleDb, but not when <x>=Sql.
//            // Password is not passed here if using "Integrated Windows Authentication"
//            //myConn.ConnectionString = "Provider=SQLOLEDB.1; Data Source=localhost;" + " Initial Catalog=Pubs; Integrated Security=SSPI";
//            myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass";
//// Making the connection
//try{
//   myConn.Open();
//   lblInfo.Text="<b>Server Version: </b>" + myConn.ServerVersion;
//   lblInfo.Text+="<br><b> Connection is:</b> " + myConn.State.ToString();
//} catch(Exception err) {
//    lblInfo.Text = "Error reading the database.";
//    lblInfo.Text += err.Message;
//} finally {
//    myConn.Close();
//    lblInfo.Text += "<br> Now Connection is";
//    lblInfo.Text += myConn.State.ToString();
//}        
        
        }
    }
}
